package com.pub.database;

import java.util.*;
import java.sql.*;

import com.pub.database.oracle.OracleFunction;
import com.pub.database.access.AccessFunction;
import com.pub.database.sqlite.SqliteFunction;
import com.pub.tools.*;
import com.pub.web.WebFunction;

public class DBFunction {
    int gintRowCount = 0;

    public DBFunction() {
    }

    /**
     * 取得資料庫中所有User
     *
     * @param dbmd DatabaseMetaData 可使用Connection.getDatabaseMetaData()取得
     * @param pblnDebug boolean
     * @return Hashtable<String,Hashtable<String,Object>>
     * @throws Exception
     */
    public static Hashtable<String,Hashtable<String,Object>> getDBUsers(DatabaseMetaData dbmd,boolean pblnDebug) throws Exception{
        ResultSet rs = null;

        try{
            rs = dbmd.getSchemas();

            return transferResultSetToHashtable(rs,rs.getMetaData(),pblnDebug,"DBFunction.getDBUsers(DatabaseMetaData,boolean)");
        }
        catch(Exception e){
            if(pblnDebug){
                e.printStackTrace();
                throw new Exception(e.fillInStackTrace());
            }
            return null;
        }
        finally{
            if(rs!=null){
                try{rs.close();rs=null;}catch(Exception e){throw new Exception(e);}
            }
        }
    }

    /**
     * 取得資料庫中的物件名稱
     *
     * @param dbmd DatabaseMetaData 可使用Connection.getDatabaseMetaData()取得
     * @param pblnDebug boolean
     * @param pstrSchemaName String OWNER NAME(user)
     * @param pstrTypes String[] Value 值可為 "TABLE", "VIEW", "SYSTEM
     *   TABLE","GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
     * @return Hashtable<String,Hashtable<String,Object>>
     * @throws Exception
     */
    public static Hashtable<String,Hashtable<String,Object>> getDBObjects(DatabaseMetaData dbmd,boolean pblnDebug,String pstrSchemaName,String[] pstrTypes) throws Exception{
        ResultSet rs = null;

        try{
            rs = dbmd.getSchemas();

            return transferResultSetToHashtable(dbmd.getTables(null,pstrSchemaName,null,pstrTypes),dbmd.getTables(null,pstrSchemaName,null,pstrTypes).getMetaData(),pblnDebug,"DBFunction.getDBObjects(DatabaseMetaData,boolean,String,String[])");
        }
        catch(Exception e){
            if(pblnDebug){
                e.printStackTrace();
                throw new Exception(e.fillInStackTrace());
            }
            return null;
        }
        finally{
            if(rs!=null){
                try{rs.close();rs=null;}catch(Exception e){throw new Exception(e);}
            }
        }
    }

    /**
     * 取得資料表中的欄位資訊
     *
     * @param dbmd DatabaseMetaData 可使用Connection.getDatabaseMetaData()取得
     * @param pblnDebug boolean
     * @param pstrSchemaName String OWNER NAME(user)
     * @param pstrTableName String
     * @return Hashtable<String,Hashtable<String,Object>>
     * @throws Exception
     */
    public static Hashtable<String,Hashtable<String,Object>> getTableColumns(DatabaseMetaData dbmd,boolean pblnDebug,String pstrSchemaName,String pstrTableName) throws Exception {
        ResultSet rstColumns = null;
        Hashtable<String,Hashtable<String,Object>> lhtRows = null;
        Hashtable<String,Object> lhtCols = null;
        int lintRowNum=0;

        try{
            rstColumns = dbmd.getColumns(null,pstrSchemaName,pstrTableName,null);

            lhtRows = new Hashtable<String, Hashtable<String, Object>>();
            //lhtCols = new Hashtable();

            while(rstColumns.next()){
                lhtCols = new Hashtable<String, Object>();

                lhtCols.put("TABLE_SCHEM", rstColumns.getObject("TABLE_SCHEM"));
                lhtCols.put("TABLE_NAME", rstColumns.getObject("TABLE_NAME"));
                lhtCols.put("COLUMN_NAME", rstColumns.getObject("COLUMN_NAME"));
                lhtCols.put("TYPE_NAME", rstColumns.getObject("TYPE_NAME"));
                lhtCols.put("COLUMN_SIZE", rstColumns.getInt("COLUMN_SIZE"));
                lhtCols.put("NUM_PREC_RADIX", rstColumns.getObject("NUM_PREC_RADIX"));
                lhtCols.put("NULLABLE", rstColumns.getObject("NULLABLE"));
                lhtCols.put("CHAR_OCTET_LENGTH", rstColumns.getObject("CHAR_OCTET_LENGTH"));
                lhtCols.put("ORDINAL_POSITION", rstColumns.getObject("ORDINAL_POSITION"));
                lhtCols.put("DECIMAL_DIGITS", rstColumns.getInt("DECIMAL_DIGITS"));

                if(!rstColumns.getObject("TYPE_NAME").toString().toLowerCase().equals("BLOB") && !rstColumns.getObject("TYPE_NAME").toString().toLowerCase().equals("CLOB")){
                    lhtCols.put("COLUMN_DEF", rstColumns.getObject("COLUMN_DEF"));
                }

                //以下在JDK Help有列出，但get時會有Exception
                //lhtCols.put("TABLE_CAT",rstColumns.getObject("TABLE_CAT"));
                //lhtCols.put("REMARKS", rstColumns.getObject("REMARKS"));
                //
                //lhtCols.put("IS_NULLABLE", rstColumns.getObject("IS_NULLABLE"));
                //lhtCols.put("SCOPE_CATLOG", rstColumns.getObject("SCOPE_CATLOG"));
                //lhtCols.put("SCOPE_SCHEMA", rstColumns.getObject("SCOPE_SCHEMA"));
                //lhtCols.put("SCOPE_TABLE", rstColumns.getObject("SCOPE_TABLE"));
                //lhtCols.put("SOURCE_DATA_TYPE", rstColumns.getObject("SOURCE_DATA_TYPE"));

                lhtRows.put(String.valueOf(lintRowNum),(Hashtable<String, Object>)lhtCols);

                lhtCols = null;
                //lhtCols.clear();

                lintRowNum++;
            }

            return lhtRows;
        }
        catch(Exception e){
            if(pblnDebug){
                e.printStackTrace();
            }
            throw new Exception(e.fillInStackTrace());
        }
        finally{
            if(rstColumns != null){
                rstColumns.close();
                rstColumns = null;
            }
        }

        //return transferResultSetToHashtable(dbmd.getColumns(null,pstrSchemaName,pstrTableName,null),dbmd.getColumns(null,pstrSchemaName,pstrTableName,null).getMetaData(),pblnDebug,"DBFunction.getTableColumns(DatabaseMetaData,boolean,String,String)");
    }

    /**
     * 取得資料表中的主鍵資訊
     *
     * @param dbmd DatabaseMetaData 可使用Connection.getDatabaseMetaData()取得
     * @param pblnDebug boolean
     * @param pstrSchemaName String OWNER NAME(user)
     * @param pstrTableName String
     * @return Hashtable<String,Hashtable<String,Object>>
     * @throws Exception
     */
    public static Hashtable<String,Hashtable<String,Object>> getTablePrimaryKey(DatabaseMetaData dbmd,boolean pblnDebug,String pstrSchemaName,String pstrTableName) throws Exception {
        ResultSet rs = null;

        try{
            rs = dbmd.getSchemas();

            return transferResultSetToHashtable(dbmd.getPrimaryKeys(null,pstrSchemaName,pstrTableName),dbmd.getPrimaryKeys(null,pstrSchemaName,pstrTableName).getMetaData(),pblnDebug,"DBFunction.getTablePrimaryKey(DatabaseMetaData,boolean,String,String)");
        }
        catch(Exception e){
            if(pblnDebug){
                e.printStackTrace();
                throw new Exception(e.fillInStackTrace());
            }
            return null;
        }
        finally{
            if(rs!=null){
                try{rs.close();rs=null;}catch(Exception e){throw new Exception(e);}
            }
        }
    }

    /**
     * 取得資料表中外來鍵資訊
     *
     * @param dbmd DatabaseMetaData 可使用Connection.getDatabaseMetaData()取得
     * @param pblnDebug boolean
     * @param pstrSchemaName String OWNER NAME(user)
     * @param pstrTableName String
     * @return Hashtable<String,Hashtable<String,Object>>
     * @throws Exception
     */
    public static Hashtable<String,Hashtable<String,Object>> getTableForeignKey(DatabaseMetaData dbmd,boolean pblnDebug,String pstrSchemaName,String pstrTableName) throws Exception {
        ResultSet rs = null;

        try{
            rs = dbmd.getSchemas();

            return transferResultSetToHashtable(dbmd.getImportedKeys(null,pstrSchemaName,pstrTableName),dbmd.getExportedKeys(null,pstrSchemaName,pstrTableName).getMetaData(),pblnDebug,"DBFunction.getTableForeignKey(DatabaseMetaData,boolean,String,String)");
        }
        catch(Exception e){
            if(pblnDebug){
                e.printStackTrace();
                throw new Exception(e.fillInStackTrace());
            }
            return null;
        }
        finally{
            if(rs!=null){
                try{rs.close();rs=null;}catch(Exception e){throw new Exception(e);}
            }
        }
    }

    /**
     * 取得資料表中索引鍵資訊
     *
     * @param dbmd DatabaseMetaData 可使用Connection.getDatabaseMetaData()取得
     * @param pblnDebug boolean
     * @param pstrSchemaName String OWNER NAME(user)
     * @param pstrTableName String
     * @return Hashtable<String,Hashtable<String,Object>>
     * @throws Exception
     */
    public static Hashtable<String,Hashtable<String,Object>> getIndexInfo(DatabaseMetaData dbmd,boolean pblnDebug,String pstrSchemaName,String pstrTableName) throws Exception{
        ResultSet rs = null;

        try{
            rs = dbmd.getSchemas();

            return transferResultSetToHashtable(dbmd.getIndexInfo(null,pstrSchemaName,pstrTableName,false,false),dbmd.getIndexInfo(null,pstrSchemaName,pstrTableName,false,false).getMetaData(),pblnDebug,"DBFunction.getTableIndexInfo(DatabaseMetaData,boolean,String,String,boolean)");
        }
        catch(Exception e){
            if(pblnDebug){
                e.printStackTrace();
                throw new Exception(e.fillInStackTrace());
            }
            return null;
        }
        finally{
            if(rs!=null){
                try{rs.close();rs=null;}catch(Exception e){throw new Exception(e);}
            }
        }
    }

    /**
     * 將insert into sql 自動加入參數代碼<br>
     * 串入sql語法的(?,?) 取代人工輸入可能的錯誤，但一定要詳述欄位名稱，並且要有逗號才行
     * @param strTemp Insert into SQL
     * @return String 已串好參數符號(?)的SQL
     * @sample PublicCheck.addQuemark("Insert into KM_ATTACH (FLOW_NUM,ID) values");<br>
     * 傳回值:Insert into KM_ATTACH (FLOW_NUM,ID) values (?,?)
     */
    public static String addQuemark(String strTemp) {
        int j = 0;
        StringBuffer stbTemp = new StringBuffer();
        for (int i = 0; i < strTemp.length(); i++) {
            if (strTemp.substring(i, i + 1).equals(",")) {
                j++;
            }
        }
        if (j > 0) {
            stbTemp.append(" (");
            for (int i = 0; i <= j; i++) {
                if (i == 0) {
                    stbTemp.append("?");
                } else {
                    stbTemp.append(",?");
                }
            }
            stbTemp.append(")");
        }
        return stbTemp.toString();
    }

    /**
     * 取得日期轉換為字串的DB函數
     * @param pstrDBType String DataBase Type ex.ORACLE,ACCESS etc.,
     * @param pstrFldName String 要轉換的日期欄位名稱
     * @param pstrFormat String 轉換出來的字串格式
     * @return String
     * @sample String strSQL="select " + DBFunction.getDateToStrSyntan("ORACLE","BIRTH_DATE","yyyy/MM/dd") + " from pnl_employee";
     */
    public static String getDateToStrSyntax(String pstrDBType,
                                            String pstrFldName,
                                            String pstrFormat) {
        StringBuffer lstbSyntax = new StringBuffer();
        StringBuffer lstbFormat = new StringBuffer();

        if (pstrDBType.equalsIgnoreCase("ORACLE")) {
            lstbSyntax.append("to_char(");
            lstbFormat.append(pstrFormat.toString());
            
            lstbSyntax.append(pstrFldName).append(",'").append(lstbFormat.
                    toString()).append("')");
        } else if (pstrDBType.equalsIgnoreCase("ACCESS")) {
            lstbSyntax.append("format(");
            lstbFormat.append(PublicFunction.replace(pstrFormat, "hh24", "hh", true));
            
            lstbSyntax.append(pstrFldName).append(",'").append(lstbFormat.
                    toString()).append("')");
        } else if (pstrDBType.equalsIgnoreCase("SQLITE")) {
        	//sqlite 尚未提供類似的function
        	lstbSyntax.append(pstrFldName);
        }
        return lstbSyntax.toString();
    }

    /**
     * 依資料庫型態取得sql 連接字串的語法 ex.oracle--||,access--&
     *
     * @param pstrDBType String
     * @return String
     */
    public static String getConStrSybtax(String pstrDBType) {
        if (pstrDBType.equalsIgnoreCase("ORACLE")) {
            return "||";
        } else if (pstrDBType.equalsIgnoreCase("ACCESS")) {
            return "&";
        }
        else if (pstrDBType.equalsIgnoreCase("SQLITE")) {
            return "||";
        }
        return "";
    }

    /**
     * 依資料庫型態取得like 萬用字元的符號 ex.oracle--%,access--*
     *
     * @param pstrDBType String
     * @return String
     */
    public static String getLikeSyntax(String pstrDBType) {
        if (pstrDBType.equalsIgnoreCase("ORACLE")) {
            return "%";
        } else if (pstrDBType.equalsIgnoreCase("ACCESS")) {
            return "*";
        } else if (pstrDBType.equalsIgnoreCase("SQLITE")) {
            return "%";
        }
        return "";
    }

    /**
     * 取得日期轉換為字串的DB函數
     * @param pstrDBType String DataBase Type ex.ORACLE,ACCESS etc.,
     * @param pstrFldName String 要轉換的日期欄位名稱
     * @param pstrFormat String 轉換出來的字串格式
     * @return String
     * @sample String strSQL="select " + DBFunction.getDateToStrSyntan("ORACLE","BIRTH_DATE","yyyy/MM/dd") + " from pnl_employee";
     */
    public static String getNullToValueSyntax(String pstrDBType,
                                              String pstrFieldName,
                                              String pstrReplace) {
        StringBuffer lstbSyntax = new StringBuffer();

        if (pstrDBType.equalsIgnoreCase("ORACLE")) {
            lstbSyntax.append("nvl(");
            lstbSyntax.append(pstrFieldName);
            lstbSyntax.append(",");
            lstbSyntax.append(pstrReplace);
            lstbSyntax.append(")");
        } else if (pstrDBType.equalsIgnoreCase("ACCESS")) {
            lstbSyntax.append("iif(isnull(");
            lstbSyntax.append(pstrFieldName);
            lstbSyntax.append("),");
            lstbSyntax.append(pstrReplace);
            lstbSyntax.append(",");
            lstbSyntax.append(pstrFieldName);
            lstbSyntax.append(")");
        } else if (pstrDBType.equalsIgnoreCase("SQLITE")) {
            lstbSyntax.append("ifnull(");
            lstbSyntax.append(pstrFieldName);
            lstbSyntax.append(",");
            lstbSyntax.append(pstrReplace);
            lstbSyntax.append(") ");
        }
        return lstbSyntax.toString();
    }


    public static String getDBType(String pstrDBAlias) {
    	if(pstrDBAlias.toUpperCase().indexOf("SQLITE:")==0){
    		return "SQLITE";
    	}
    	else{
	        return ResourceBundle.getBundle("DB_Config").getString(pstrDBAlias +
	                "_DB");
    	}
    }

    /**
     * 依資料庫型態取得sql 去除字串空白的語法 ex.oracle--trim
     *
     * @param pstrDBType String
     * @param pstrFldName String
     * @return String
     */
    public static String getTrimSyntax(String pstrDBType, String pstrFldName) {
        StringBuffer lstbSyntax = new StringBuffer();

        if (pstrDBType.toUpperCase().equals("ORACLE") ||
            pstrDBType.toUpperCase().equals("ACCESS") ||
            pstrDBType.toUpperCase().equals("SQLITE")) {
            lstbSyntax.append("trim(");
            lstbSyntax.append(pstrFldName);
            lstbSyntax.append(")");
        }

        return lstbSyntax.toString();
    }
    
    /**
     * 依資料庫型態取得特定筆數的sql syntax(ex SQLite -- limit 0,10)
     *
     * @param pstrDBType String
     * @param pstrFldName String
     * @return String
     * @throws SQLException 
     */
    public static String getLimitRowSql(String pstrDBType,String pstrSQL,long plngStartRow,long plngRowCount) throws Exception {
        String lstrSyntax = null;

        try{
	        if (pstrDBType.toUpperCase().equals("ORACLE")) {
	        	lstrSyntax=OracleFunction.incPageSizeSQL(pstrSQL, plngStartRow, plngStartRow + plngRowCount);
	        }
	        else if (pstrDBType.toUpperCase().equals("ACCESS")) {
	        	lstrSyntax  = AccessFunction.incPageSizeSQL(pstrSQL, plngStartRow, plngRowCount);
	        }
	        else if (pstrDBType.toUpperCase().equals("SQLITE")) {
	        	lstrSyntax = SqliteFunction.incPageSizeSQL(pstrSQL, plngStartRow, plngRowCount);
	        }
	
	        if(lstrSyntax == null){
	        	throw new SQLException("the " + pstrDBType + " db type can't support limit row sql!!");
	        }
	        
	        return lstrSyntax;
        }
        catch(Exception ex){
        	throw new Exception(ex);
        }
    }   

    static Hashtable<String,Hashtable<String,Object>> transferResultSetToHashtable(ResultSet rs,
                                                  ResultSetMetaData rsmd,
                                                  boolean blnDebug,
                                                  String pstrCaller) throws
            Exception {
        Hashtable<String, Hashtable<String,Object>> hstDataTmp = null; //回傳用的 Hashtable
        String strColType = "";
        String strColLabel = "";
        Object objTemp = null;
        Hashtable<String, Object> hstTemp = null;
        Object lobjData = null;
        int lintRowCount=0;

        try {
            //取得欄位數。
            int numcols = rsmd.getColumnCount();

            //rs = db.querySQL(strSql);
            while (rs.next()) {
                if (hstDataTmp == null) {
                    hstDataTmp = new Hashtable<String, Hashtable<String,Object>>();
                }
                if (hstTemp == null) {
                    hstTemp = new Hashtable<String, Object>();
                }
                for (int j = 0; j < numcols; j++) {
                    strColType = (String) rsmd.getColumnTypeName(j + 1).trim();
                    strColLabel = (String) rsmd.getColumnLabel(j + 1).trim();
                    //if(getDBDebugMode())System.out.println("strColType==>"+strColType);
                    //System.out.println("strColLabel==>"+strColLabel + "   strColType==>"+strColType);
                    lobjData = rs.getObject(j + 1);

                    if (lobjData == null) { //如果欄位值是null時，數字轉成0，字串轉成空白
                    	if (strColType==null || strColType.equals("null")){ //sqlite當欄位值是null時則會連colType都是null
                    		objTemp = "";
                    	}
                    	else if (strColType.equalsIgnoreCase("NUMBER") ||
                            strColType.equalsIgnoreCase("FLOAT") ||
                            strColType.equalsIgnoreCase("LONG") ||
                            strColType.substring(0, 3).equalsIgnoreCase("DEC")) {
                            objTemp = "0";
                        } 
                    	else if (strColType.equalsIgnoreCase("CHAR") ||
                                   strColType.equalsIgnoreCase("TEXT") ||
                                   strColType.equalsIgnoreCase("VARCHAR") ||
                                   strColType.equalsIgnoreCase("VARCHAR2") ||
                                   strColType.equalsIgnoreCase("DATE") ||
                                   strColType.equalsIgnoreCase("LONGCHAR")) {
                            objTemp = "";
                        } 
                    	else {
                            objTemp = null;
                        }
                    } else {
                        if (strColType.equalsIgnoreCase("CHAR") ||
                            strColType.equalsIgnoreCase("VARCHAR2") ||
                            strColType.equalsIgnoreCase("LONGCHAR") ||
                            strColType.equalsIgnoreCase("VARCHAR") ||
                            strColType.equalsIgnoreCase("TEXT")) {
                            objTemp = WebFunction.replaceReserveCharOnWebPage(
                                    lobjData.toString().trim());
                        } else {
                            objTemp = lobjData;
                        }
                    }
                    hstTemp.put(strColLabel.toUpperCase(), objTemp); //將欄位名稱以大寫填入Hashtable中，以免混淆
                } //End - for
                hstDataTmp.put(String.valueOf(lintRowCount), (Hashtable<String,Object>) hstTemp);
                hstTemp = null;
                lintRowCount++;
            } //end - while

            if (blnDebug) {
                System.out.println(
                        pstrCaller + " --> rowcount=" +
                        lintRowCount + " numcols=" + numcols);
            }

            return hstDataTmp;
        } catch (Exception e) {
            System.out.println(
                    "DBFunction.transferResultSetToHashtable (ResultSet) --> caller ==> " +
                    pstrCaller);
            throw new Exception(e);
        } finally {
            //釋放沒用的記憶體空間
            if (lobjData != null) {
                lobjData = null;
            }
            if (objTemp != null) {
                objTemp = null;
            }
            if (hstTemp != null) {
                hstTemp = null; //釋放沒用的Hashtable
            }
            if (rsmd != null) {
                rsmd = null; //釋放沒用的ResultSetMetaData
            }

        }
    }

    static ArrayList<String> getResultSetColumn(ResultSetMetaData rsmd,boolean blnDebug,String pstrCaller) throws
            Exception {
        ArrayList<String> larylstColumn = null;

        try {
            larylstColumn = new ArrayList<String>();

            //取得欄位數。
            int numcols = rsmd.getColumnCount();
            //取得欄位名稱
            for (int i = 0; i < numcols; i++) {
                if (!String.valueOf(rsmd.getColumnLabel(i + 1)).toUpperCase().
                    equals(
                            "rnm_special".toUpperCase())) {
                    larylstColumn.add(String.valueOf(rsmd.getColumnLabel(i +
                            1)).toUpperCase());
                }

                if(blnDebug){
                    System.out.print((i == 0 ? pstrCaller + " --> " : ",") + "COL" +
                                     String.valueOf(i) + "=" +
                                     String.valueOf(rsmd.getColumnLabel(i + 1)) +
                                     (i == numcols - 1 ? "\n" : ""));
                }
            }

            return larylstColumn;
        } catch (Exception e) {
            throw new Exception(e);
        }
    }

    public static ArrayList<String> getAllDbAliasList() throws Exception{
        ArrayList<String> arlDBAliasList = new ArrayList<String>();
        String lstrKeyName=null;
        ResourceBundle rdbDB_Config = null;
        Enumeration<String> eunKeys = null;

        try{
            rdbDB_Config = ResourceBundle.getBundle("DB_Config", Locale.getDefault());
            eunKeys = rdbDB_Config.getKeys();

            while (eunKeys.hasMoreElements()) {
                lstrKeyName = eunKeys.nextElement().toString();

                if (lstrKeyName.indexOf("_DB") > 0 && lstrKeyName.length() == lstrKeyName.indexOf("_DB") + 3) {
                    arlDBAliasList.add(lstrKeyName.replaceAll("_DB", ""));
                }
            }

            Collections.sort(arlDBAliasList);
        }
        catch(Exception e){
            throw new Exception(e.fillInStackTrace());
        }
        finally{
            if(rdbDB_Config!=null){
                rdbDB_Config=null;
            }
        }
        return arlDBAliasList;
    }
    
    public Hashtable<String,String> splitSQL(String pstrSQL){
    	Hashtable<String,String> lhtSqlInfo = new Hashtable<String,String>();
    	String lstrSQL = pstrSQL.toLowerCase();
    	
    	if(lstrSQL.lastIndexOf(" asc")>0){
    		lhtSqlInfo.put("OrderBySeq","asc");
    	}
    	else if(lstrSQL.lastIndexOf(" desc")>0){
    		lhtSqlInfo.put("OrderBySeq","desc");
    	}
    	
    	if(lhtSqlInfo.containsKey("OrderBySeq")){
    		lhtSqlInfo.put("OrderByFields", pstrSQL.substring(lstrSQL.lastIndexOf("order by")+8,lstrSQL.lastIndexOf(lhtSqlInfo.get("OrderByFields").toString())-1));
    	}
    	
    	if(lstrSQL.lastIndexOf("having")>0){
    		lhtSqlInfo.put("Having", pstrSQL.substring(lstrSQL.lastIndexOf("having"),lstrSQL.lastIndexOf("order by")>0 ? lstrSQL.lastIndexOf("order by")-1 : lstrSQL.length()));
    	}
    	
    	return lhtSqlInfo;
    }
}
